<?php
class Kardex_model extends CI_Model {

   
   // private $_tabla = 'products';

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

      
    public function search($code = null, $id_warehouse = null, $desde = null, $hasta = null)
    {
        
        $query = $this->db->query("(SELECT sa.date, sa.document_type_id, sa.document_code,(SELECT name FROM clients WHERE id = sa.id_client) as client,  sad.box, sad.pack, sad.unit 
	FROM sales sa JOIN sales_kardex sad ON sa.document_code = sad.document_code
        WHERE sad.product_code = '$code'
        and sad.id_warehouse = '$id_warehouse' 
        and sa.date BETWEEN '$desde' and '$hasta')
        
        UNION
        
        (SELECT tr.date, tr.document_type_id, tr.document_code,trd.id_warehouse_o as id_warehouse,  trd.box, trd.pack, trd.unit 
	FROM transfer tr JOIN transfer_details trd ON tr.document_code = trd.document_code
        WHERE trd.product_code = '$code'
        and (trd.id_warehouse_o = '$id_warehouse' or trd.id_warehouse_d = '$id_warehouse')            
        and tr.date BETWEEN '$desde' and '$hasta')

        UNION

        (SELECT sh.date, sh.document_type_id, sh.document_code,(SELECT name FROM company WHERE id_company = sh.id_supplier) as client,  sd.box, sd.pack, sd.unit 
        FROM shop sh JOIN shop_kardex sd ON sh.document_code = sd.document_code
        WHERE sd.product_code = '$code'
        and sd.id_warehouse = '$id_warehouse' 
        and sh.date BETWEEN '$desde' and '$hasta')

        ORDER BY 1,2,3;");
        $rs = $query->result();
        return $rs;
     }

     public function search_report($desde = null, $hasta = null)
    {
        
        $query = $this->db->query("SELECT s.date,s.document_code, (SELECT name from clients WHERE id = s.id_client) as cliente, s.amount FROM sales s 
        WHERE s.date BETWEEN '$desde' and '$hasta'  

        ORDER BY 1");
        $rs = $query->result();
        return $rs;
     }
}